package com.course.servlet;

import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.course.db.ChooseCourse;
import com.course.db.CourseBasic;
import com.course.safe.MD5;
import com.jspsmart.upload.SmartUpload;

/**
 * Servlet implementation class download
 */
public class download extends HttpServlet {
	private static final long serialVersionUID = 1L;
	private ServletConfig config;
	 final public void init(ServletConfig config) throws ServletException {
	  this.config = config;
	 }
	 
    /**
     * @see HttpServlet#HttpServlet()
     */
    public download() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doPost(request,response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		
		String type=request.getParameter("type");
		
		if(type.equals("classchoosecourse"))
		{
			String grade = request.getParameter("grade");
			downgrade(request,response,grade);
		}
		else if(type.equals("coursesign"))
		{
			int courseId = Integer.parseInt(request.getParameter("courseId"));
			coursesign(request,response,courseId);
		}
		else if(type.equals("daycourse"))
		{
			String grade = request.getParameter("grade");
			String day = request.getParameter("day");
			daycourse(request,response,grade,day);
		}
		else if(type.equals("dayteacher"))
		{
			String grade = request.getParameter("grade");
			String day = request.getParameter("day");
			dayteacher(request,response,grade,day);
		}
		else if(type.equals("gradecourse"))
		{
			System.out.println("gradecourse");
			String grade = request.getParameter("grade");
			gradecourse(request,response,grade);
		}
		
	}
	
	void downgrade(HttpServletRequest request, HttpServletResponse response, String grade)
	{
		ChooseCourse choosecourse = new ChooseCourse();
		ResultSet studentRs = choosecourse.show_grade_course(grade);
		String templatepath=request.getSession().getServletContext().getRealPath("/files/template/");
		String downpath=request.getSession().getServletContext().getRealPath("/files/download/");
		
		try{
			 Workbook wb = Workbook.getWorkbook(new File(templatepath+"/classchoosecourse.xls"));
			 // 第二步：通过模板得到一个可写的Workbook：第一个参数是一个输出流对象,第二个参数代表了要读取的模板
			 MD5 oMD5 = new MD5();

			 Date date = new Date();
			 SimpleDateFormat sf = new SimpleDateFormat("yyyy-mm-dd");
			 
			 File targetFile = new File(downpath+"/"+oMD5.getMD5ofStr(grade+sf.format(date))+".xls");
			 WritableWorkbook wwb = Workbook.createWorkbook(targetFile, wb);
			 // 第三步：选择模板中名称为StateResult的Sheet：
			 WritableSheet wws = wwb.getSheet(0);
			 // 第四步：选择单元格，写入动态值，根据单元格的不同类型转换成相应类型的单元格：
			 // (列，行)
			 Label A1 = (Label) wws.getWritableCell(0, 0);
			 A1.setString("温岭中学2012学年第二学期"+grade+"班学生选修课第2时段安排");
			 Label A2 = (Label) wws.getWritableCell(0, 1);
			 
			 A2.setString(sf.format(date));
			 
			 int i=3;
			 while(studentRs.next())
			 {
				 Label Aa1 = new Label(0, i, studentRs.getString("grade"));
			     wws.addCell(Aa1);
			     Label Aa2 = new Label(1, i, studentRs.getString("stu_name"));
			     wws.addCell(Aa2);
			     Label Aa3 = new Label(2, i, studentRs.getString("cou_name"));
			     wws.addCell(Aa3);
			     Label Aa4 = new Label(3, i, studentRs.getString("teach_location"));
			     wws.addCell(Aa4);
			     Label Aa5 = new Label(4, i, studentRs.getString("teach_time"));
			     wws.addCell(Aa5);
				 
				 i++;
			 }
			 
			 wwb.write();
			 wwb.close();
			 wb.close();
			 
			 SmartUpload mySmartUpload = new SmartUpload();
			 mySmartUpload.initialize(config, request, response);
			 mySmartUpload.setContentDisposition(null);
			 mySmartUpload.downloadFile("/files/download/"+oMD5.getMD5ofStr(grade+sf.format(date))+".xls");
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	void coursesign(HttpServletRequest request, HttpServletResponse response, int courseId)
	{
		ChooseCourse choosecourse = new ChooseCourse();
		CourseBasic course = new CourseBasic();
		course.setId(courseId);
		course.init();
		
		String templatepath=request.getSession().getServletContext().getRealPath("/files/template/");
		String downpath=request.getSession().getServletContext().getRealPath("/files/download/");
		MD5 oMD5 = new MD5();
		Date date = new Date();
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-mm-dd");
		
		try{
			 Workbook wb = Workbook.getWorkbook(new File(templatepath+"/coursesign.xls"));
			 // 第二步：通过模板得到一个可写的Workbook：第一个参数是一个输出流对象,第二个参数代表了要读取的模板
			 File targetFile = new File(downpath+"/"+oMD5.getMD5ofStr(course.getName()+sf.format(date))+".xls");
			 WritableWorkbook wwb = Workbook.createWorkbook(targetFile, wb);
			 // 第三步：选择模板中名称为StateResult的Sheet：
			 WritableSheet wws = wwb.getSheet(0);
			 // 第四步：选择单元格，写入动态值，根据单元格的不同类型转换成相应类型的单元格：
			 // (列，行)
			 Label A1 = (Label) wws.getWritableCell(0, 0);
			 A1.setString("温岭中学2012学年第2时段选修课安排");
			 Label A2 = (Label) wws.getWritableCell(0, 1);
			 A2.setString(course.getTeachers()+" "+course.getName());
			 Label A3 = (Label) wws.getWritableCell(0, 2);
			 A3.setString("地点： "+course.getTeach_location());
			 
			 ResultSet studentRs = choosecourse.show_course_student(courseId);
			 int i=4;
			 while(studentRs.next())
			 {
				 Label Aa1 = new Label(0, i, studentRs.getString("grade"));
			     wws.addCell(Aa1);
			     Label Aa2 = new Label(1, i, studentRs.getString("name"));
			     wws.addCell(Aa2);

				 i++;
			 }
			 
			 wwb.write();
			 wwb.close();
			 wb.close();
			 
			 SmartUpload mySmartUpload = new SmartUpload();
			 mySmartUpload.initialize(config, request, response);
			 mySmartUpload.setContentDisposition(null);
			 mySmartUpload.downloadFile(downpath+"/"+oMD5.getMD5ofStr(course.getName()+sf.format(date))+".xls");
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	void daycourse(HttpServletRequest request, HttpServletResponse response, String grade, String day)
	{
		CourseBasic course = new CourseBasic();
		ResultSet courseRs = course.show_gradeday_course(grade, day);
		String templatepath=request.getSession().getServletContext().getRealPath("/files/template/");
		String downpath=request.getSession().getServletContext().getRealPath("/files/download/");
		MD5 oMD5 = new MD5();
		Date date = new Date();
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-mm-dd");
		try{
			 Workbook wb = Workbook.getWorkbook(new File(templatepath+"/daycourse.xls"));
			 // 第二步：通过模板得到一个可写的Workbook：第一个参数是一个输出流对象,第二个参数代表了要读取的模板
			 File targetFile = new File(downpath+"/"+oMD5.getMD5ofStr(grade+day+sf.format(date))+".xls");
			 WritableWorkbook wwb = Workbook.createWorkbook(targetFile, wb);
			 // 第三步：选择模板中名称为StateResult的Sheet：
			 WritableSheet wws = wwb.getSheet(0);
			 // 第四步：选择单元格，写入动态值，根据单元格的不同类型转换成相应类型的单元格：
			 // (列，行)
			 Label A1 = (Label) wws.getWritableCell(0, 0);
			 A1.setString("温岭中学2012学年"+grade+day+"第2时段课程安排情况");
			 
			 int i=3;
			 while(courseRs.next())
			 {
				 Label Aa1 = new Label(0, i, (new Integer(i-3)).toString());
			     wws.addCell(Aa1);
			     Label Aa2 = new Label(1, i, courseRs.getString("teach_location"));
			     wws.addCell(Aa2);
			     Label Aa3 = new Label(2, i, courseRs.getString("name"));
			     wws.addCell(Aa3);
			     Label Aa4 = new Label(3, i, courseRs.getString("teach_time"));
			     wws.addCell(Aa4);
			     Label Aa5 = new Label(4, i, courseRs.getString("teachers"));
			     wws.addCell(Aa5);
			     Label Aa6 = new Label(5, i, courseRs.getString("num_of_students"));
			     wws.addCell(Aa6);
				 
				 i++;
			 }
			 
			 wwb.write();
			 wwb.close();
			 wb.close();
			 
			 SmartUpload mySmartUpload = new SmartUpload();
			 mySmartUpload.initialize(config, request, response);
			 mySmartUpload.setContentDisposition(null);
			 mySmartUpload.downloadFile(downpath+"/"+oMD5.getMD5ofStr(grade+day+sf.format(date))+".xls");
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	void dayteacher(HttpServletRequest request, HttpServletResponse response, String grade, String day)
	{
		CourseBasic course = new CourseBasic();
		ResultSet courseRs = course.show_gradeday_course(grade, day);
		String templatepath=request.getSession().getServletContext().getRealPath("/files/template/");
		String downpath=request.getSession().getServletContext().getRealPath("/files/download/");
		MD5 oMD5 = new MD5();
		Date date = new Date();
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-mm-dd");
		try{
			 Workbook wb = Workbook.getWorkbook(new File(templatepath+"/dayteacher.xls"));
			 // 第二步：通过模板得到一个可写的Workbook：第一个参数是一个输出流对象,第二个参数代表了要读取的模板
			 File targetFile = new File(downpath+"/teacher"+oMD5.getMD5ofStr(grade+sf.format(date))+".xls");
			 WritableWorkbook wwb = Workbook.createWorkbook(targetFile, wb);
			 // 第三步：选择模板中名称为StateResult的Sheet：
			 WritableSheet wws = wwb.getSheet(0);
			 // 第四步：选择单元格，写入动态值，根据单元格的不同类型转换成相应类型的单元格：
			 // (列，行)
			 Label A1 = (Label) wws.getWritableCell(0, 0);
			 A1.setString("温岭中学2012学年"+grade+day+"第2时段教师点到情况");
			 
			 int i=3;
			 while(courseRs.next())
			 {
				 Label Aa1 = new Label(0, i, (new Integer(i-3)).toString());
			     wws.addCell(Aa1);
			     Label Aa2 = new Label(1, i, courseRs.getString("teach_location"));
			     wws.addCell(Aa2);
			     Label Aa3 = new Label(2, i, courseRs.getString("name"));
			     wws.addCell(Aa3);
			     Label Aa4 = new Label(3, i, courseRs.getString("teachers"));
			     wws.addCell(Aa4);
				 
				 i++;
			 }
			 
			 wwb.write();
			 wwb.close();
			 wb.close();
			 
			 SmartUpload mySmartUpload = new SmartUpload();
			 mySmartUpload.initialize(config, request, response);
			 mySmartUpload.setContentDisposition(null);
			 mySmartUpload.downloadFile(downpath+"/teacher"+oMD5.getMD5ofStr(grade+sf.format(date))+".xls");
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	void gradecourse(HttpServletRequest request, HttpServletResponse response, String grade )
	{
		CourseBasic course = new CourseBasic();
		ResultSet courseRs = course.show_grade_course(grade);
		String templatepath=request.getSession().getServletContext().getRealPath("/files/template/");
		String downpath=request.getSession().getServletContext().getRealPath("/files/download/");
		MD5 oMD5 = new MD5();
		Date date = new Date();
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-mm-dd");
		try{
			 Workbook wb = Workbook.getWorkbook(new File(templatepath+"/gradecourse.xls"));
			 // 第二步：通过模板得到一个可写的Workbook：第一个参数是一个输出流对象,第二个参数代表了要读取的模板
			 File targetFile = new File(downpath+"/"+oMD5.getMD5ofStr(grade+sf.format(date))+".xls");
			 WritableWorkbook wwb = Workbook.createWorkbook(targetFile, wb);
			 // 第三步：选择模板中名称为StateResult的Sheet：
			 WritableSheet wws = wwb.getSheet(0);
			 // 第四步：选择单元格，写入动态值，根据单元格的不同类型转换成相应类型的单元格：
			 // (列，行)
			 Label A1 = (Label) wws.getWritableCell(0, 0);
			 A1.setString("浙江省温岭中学2012学年"+grade+"选修课开设情况");
			
			 int i=2;
			 while(courseRs.next())
			 {
			     //Label Aa1 = new Label(0, i, (new Integer(i-2)).toString() );
			     //wws.addCell(Aa1);
			     jxl.write.Number Aa1 = new jxl.write.Number(0,i,new Integer(i-2));
			     wws.addCell(Aa1);
			     Label Aa2 = new Label(1, i, courseRs.getString("classno"));
			     wws.addCell(Aa2);
			     Label Aa3 = new Label(2, i, courseRs.getString("openterm"));
			     wws.addCell(Aa3);
			     Label Aa4 = new Label(3, i, courseRs.getString("type"));
			     wws.addCell(Aa4);
			     Label Aa5 = new Label(4, i, courseRs.getString("name"));
			     wws.addCell(Aa5);
			     Label Aa6 = new Label(5, i, courseRs.getString("teachers"));
			     wws.addCell(Aa6);
			     String status = null;
			     if(courseRs.getInt("checkstatus")==0)
			    	 status = "审核中";
			     else if(courseRs.getInt("checkstatus")==1)
			    	 status = "审核通过";
			     else if(courseRs.getInt("checkstatus")==2)
			    	 status = "审核不通过";
			     else if(courseRs.getInt("checkstatus")==3)
			    	 status = "选课中";
			     else if(courseRs.getInt("checkstatus")==4)
			    	 status = "开课中";
			     else if(courseRs.getInt("checkstatus")==5)
			    	 status = "结束";
			     Label Aa7 = new Label(6, i, status);
			     wws.addCell(Aa7);
			     String num = (new Integer(courseRs.getInt("students_low"))).toString()+"/"+(new Integer(courseRs.getInt("students_high"))).toString();
			     Label Aa8 = new Label(7, i, num );
			     wws.addCell(Aa8);
			     jxl.write.Number Aa9 = new jxl.write.Number(8,i,courseRs.getInt("course_span"));
			     wws.addCell(Aa9);
			     jxl.write.Number Aa10 = new jxl.write.Number(9,i,courseRs.getDouble("credit"));
			     wws.addCell(Aa10);
			     //Label Aa11 = new Label(10, i, courseRs.getString("teach_location"));
			     //wws.addCell(Aa11);
			     Label Aa11 = new Label(10, i, courseRs.getString("teach_time"));
			     wws.addCell(Aa11);
			     Label Aa12 = new Label(11, i, courseRs.getString("teach_location"));
			     wws.addCell(Aa12);
			     Label Aa13 = new Label(12, i, courseRs.getString("teach_style"));
			     wws.addCell(Aa13);
			     Label Aa14 = new Label(13, i, courseRs.getString("test_style"));
			     wws.addCell(Aa14);
			     Label Aa15 = new Label(14, i, courseRs.getString("introduction"));
			     wws.addCell(Aa15);
			     Label Aa16 = new Label(15, i, courseRs.getString("teach_equipments"));
			     wws.addCell(Aa16);
			     Label Aa17 = new Label(16, i, courseRs.getString("apply_date"));
			     wws.addCell(Aa17);
			     //Label Aa18 = new Label(17, i, courseRs.getString("introduction"));
			     //wws.addCell(Aa18);
			     jxl.write.Number Aa18 = new jxl.write.Number(17,i,courseRs.getInt("num_of_students"));
			     wws.addCell(Aa18);
				 
				 i++;
			 }
			 
			 wwb.write();
			 wwb.close();
			 wb.close();
			 
			 SmartUpload mySmartUpload = new SmartUpload();
			 mySmartUpload.initialize(config, request, response);
			 mySmartUpload.setContentDisposition(null);
			 mySmartUpload.downloadFile(downpath+"/"+oMD5.getMD5ofStr(grade+sf.format(date))+".xls");
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}


}
